package com.bondmatch.demo.service;

import com.bondmatch.demo.mapper.TransRequestMapper;
import com.bondmatch.demo.model.TransRequest;
import com.bondmatch.demo.utils.DefaultResponseBody;
import com.bondmatch.demo.utils.Pager;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class TransRequestService {

    @Resource
    private TransRequestMapper transRequestMapper;

    /**
     * 导入Excel文件
     * @param excelFile
     * @param fileName
     * @return
     * @throws Exception
     */
    public DefaultResponseBody importExcel(InputStream excelFile, String fileName) throws Exception {
        List<TransRequest> list = new ArrayList<TransRequest>();
        DefaultResponseBody resultMap = new DefaultResponseBody();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");//时间转换格式
        //创建Excel工作薄
        Workbook securities = this.judgeFormat(excelFile,fileName);
        Sheet sheet = securities.getSheetAt(0);

        //处理表格数据
        for(int i=1; i<sheet.getLastRowNum()+1; i++) {
            Row row = sheet.getRow(i);
            String bondCode = row.getCell(0).getStringCellValue(); //债券代码
            if (!bondCode.matches("[0-9]{1,}")){
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"债券代码错误，应为纯数字。");
                return resultMap;
            }
            else if (bondCode.length() != 6){
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"债券代码错误，长度应为6。");
                return resultMap;
            }

            String customerNo = row.getCell(1).getStringCellValue(); //客户编号
            if (!customerNo.matches("[0-9]{1,}")){
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"客户编号错误，应为纯数字。");
                return resultMap;
            }
            else if (customerNo.length() != 6){
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"客户编号错误，长度应为6。");
                return resultMap;
            }


            String transactionDateTemp = row.getCell(2).getStringCellValue();
            if (!judgeDate(transactionDateTemp.length(),transactionDateTemp,"yyyy-MM-dd HH:mm:ss")){

                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"时间格式错误，时间格式应为yyyy-MM-dd HH:mm:ss。");
                return resultMap;
            }
            Date transactionDate = formatter.parse(row.getCell(2).getStringCellValue());//交易日期

            Cell cell = row.getCell(3);
            cell.setCellType(CellType.STRING);//设置单元格格式
            String typeTemp = cell.getStringCellValue();

            Integer type = null;//交易方式
            if (typeTemp.equals("卖出")){
                type = 1;
            }else if (typeTemp.equals("买入")){
                type = 0;
            }else{
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"交易方式错误，正确格式为买入或卖出。");
                return resultMap;
            }

            BigDecimal price = BigDecimal.valueOf(row.getCell(4).getNumericCellValue());//期望价格
            if(!judgeDecimal(price)){
                resultMap.setCode(500);
                resultMap.setMessage("第"+i+"行"+"金额错误，正确格式为小数点后两位。");
                return resultMap;
            }
            list.add(new TransRequest(bondCode, customerNo, transactionDate, type, price));
        }
        //批量插入
        int num = transRequestMapper.batchInsert(list);
        resultMap.setCode(200);
        resultMap.setMessage("成功");
        //resultMap.setCount(num);
        resultMap.setData(findByPager(1,5).getRows());
        return resultMap;
    }


    /**
     * 分页查询
     * @param pageNum
     * @param pageSize
     * @return
     */
    public Pager<TransRequest> findByPager(int pageNum, int pageSize){
        Pager<TransRequest> pager = new Pager<TransRequest>();
        Page<TransRequest> res = PageHelper.startPage(pageNum, pageSize);
        transRequestMapper.selectAll();
        pager.setRows(res.getResult());
        pager.setTotal(res.getTotal());
        return pager;
    }

    /**
     * 判断文件格式.xls/.xlsx
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook judgeFormat(InputStream in, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if(".xls".equals(fileType)){
            workbook = new HSSFWorkbook(in);
        }else if(".xlsx".equals(fileType)){
            workbook = new XSSFWorkbook(in);
        }else {
            throw new Exception("请上传.xls/.xlsx格式文件！");
        }
        return workbook;
    }

    /**
     * 根据时间 和时间格式 校验是否正确
     * @param length 校验的长度
     * @param sDate 校验的日期
     * @param format 校验的格式
     * @return
     */
    public static boolean judgeDate(int length, String sDate, String format) {
        int legalLen = length;
        if ((sDate == null) || (sDate.length() != legalLen)) {
            return false;
        }
        DateFormat formatter = new SimpleDateFormat(format);
        try {
            Date date = formatter.parse(sDate);
            return sDate.equals(formatter.format(date));
        } catch (Exception e) {
            return false;
        }
    }

    //两位小数金额校验
    public static boolean judgeDecimal(Object obj) {
        boolean flag = false;
        try {
            if (obj != null) {
                String source = obj.toString();
                // 判断是否是整数或者是携带一位或者两位的小数
                Pattern pattern = Pattern.compile("^[+]?([0-9]+(.[0-9]{1,2})?)$");
                if (pattern.matcher(source).matches()) {
                    flag = true;
                }
            }
        } catch (Exception e) {
            e.getMessage();
        }
        return flag;
    }

    /**
     * 根据主键查找交易请求
     * @param id
     * @return
     */
    public TransRequest selectByPrimaryKey(Integer id) {
        return transRequestMapper.selectByPrimaryKey(id);
    }
}
